
package com.compuvest.table;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class TableMySQL {

    public static Logger log = LoggerFactory.getLogger(TableMySQL.class);

    private DataSource ds;

    public TableMySQL() {

        try {
            Context initCtx = new InitialContext();
            ds = (DataSource)initCtx.lookup("jdbc/mysql");
        }
        catch(Exception e) {
            log.error("init(): " + e);
        }

        log.info("Init: " + getMysqlVersion());
    }

    public String getMysqlVersion() {

        String result = "";
        try(Connection conn = ds.getConnection();
            PreparedStatement ps = conn.prepareStatement("SELECT VERSION() version");
            ResultSet rs = ps.executeQuery())
        {
            if(rs.next()) {
                result = rs.getString("version");
            }
        }
        catch(SQLException e) {
            log.error("getVersion(): " + e);
        }

        return result;
    }

    static final String GET_ADVERTTABLE =
        "SELECT it.accwareCatID accwareCode,it.mfr_name mfrName,it.mfr_number mfrNumber,it.sell_quantity qty,it.cost_price costPrice,it.sell_price sellPrice," +
        "ta.conditions conditions,ad.upc upc,iv.alias alias," +
        "ad.grabber graber,ad.bizrate bizrate,ad.nexttag nextag,ad.shopping shoping " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemsAdvert ad USING(item_id) " +
        "LEFT JOIN sklad.itemVendor iv USING(vendor_id) " +
        "WHERE it.item_id IS NOT NULL ";
    static final String GET_ADVERTTABLE_COUNT =
        "SELECT COUNT(*) itemsCount " +
        "FROM advert.tempItemsGroupForAdvert ta " +
        "LEFT JOIN sklad.items it USING(item_id) " +
        "LEFT JOIN sklad.itemVendor iv USING(vendor_id) " +
        "WHERE it.item_id IS NOT NULL ";

    public String getAdvertTable(HttpServletRequest request) {

        int tableOffset = 0; try { tableOffset = request.getParameter("offset") == null ? 0 : Integer.parseInt(request.getParameter("offset")); } catch(Exception ignore) { }

        String sortBy = "";
        if(request.getParameter("s0") != null) {
            sortBy = " ORDER BY it.accwareCatID " + request.getParameter("s0");
        }
        else if(request.getParameter("s1") != null) {
            sortBy = " ORDER BY it.mfr_name " + request.getParameter("s1");
        }
        else if(request.getParameter("s2") != null) {
            sortBy = " ORDER BY it.mfr_number " + request.getParameter("s2");
        }
        else if(request.getParameter("s4") != null) {
            sortBy = " ORDER BY iv.alias " + request.getParameter("s4");
        }

        String accwareCode = "";
        if(request.getParameter("f[0][0]") != null) {
            accwareCode = " AND it.accwareCatID LIKE '" + request.getParameter("f[0][0]").replace("*", "") + "%'";
        }
        String mfrName = "";
        if(request.getParameter("f[1][0]") != null) {
            mfrName = " AND it.mfr_name LIKE '" + request.getParameter("f[1][0]").replace("*", "") + "%'";
        }
        String mfrNumber = "";
        if(request.getParameter("f[2][0]") != null) {
            mfrNumber = " AND it.mfr_number LIKE '" + request.getParameter("f[2][0]").replace("*", "") + "%'";
        }
        String vendorAlias = "";
        if(request.getParameter("f[4][0]") != null) {
            vendorAlias = " AND iv.alias LIKE '" + request.getParameter("f[4][0]").replace("*", "") + "%'";
        }

        StringBuilder xml = new StringBuilder();
        try(Connection conn = ds.getConnection()) {

            xml.append("<rows update_ui='true' offset='").append(tableOffset).append("'>");

            PreparedStatement ps = conn.prepareStatement(new StringBuilder().
                append(GET_ADVERTTABLE).
                append(accwareCode).append(mfrName).append(mfrNumber).append(vendorAlias).append(sortBy).
                append(" LIMIT ").append(tableOffset).append(",51").toString());
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                    xml.append("<tr>");
                    xml.append("<td>").append(rs.getString("accwareCode")).append("</td>");
                    xml.append("<td>").append(rs.getString("mfrName")).append("</td>");
                    xml.append("<td>").append(rs.getString("mfrNumber")).append("</td>");
                    xml.append("<td>").append(rs.getString("upc")).append("</td>");
                    xml.append("<td>").append(rs.getString("alias")).append("</td>");
                    xml.append("<td>").append(rs.getString("conditions")).append("</td>");
                    xml.append("<td>").append(rs.getString("qty")).append("</td>");
                    xml.append("<td>").append(rs.getString("costPrice")).append("</td>");
                    xml.append("<td>").append(rs.getString("sellPrice")).append("</td>");
                    xml.append("<td>").append(rs.getString("graber")).append("</td>");
                    xml.append("<td>").append(rs.getString("bizrate")).append("</td>");
                    xml.append("<td>").append(rs.getString("nextag")).append("</td>");
                    xml.append("<td>").append(rs.getString("shoping")).append("</td>");
                    xml.append("</tr>");
            }
            rs.close();
            ps.close();

            xml.append("</rows>");

            ps = conn.prepareStatement(new StringBuilder().
                append(GET_ADVERTTABLE_COUNT).
                append(accwareCode).append(mfrName).append(mfrNumber).append(vendorAlias).toString());
            rs = ps.executeQuery();
            if(rs.next()) {
                xml.append("<rowcount>").append(rs.getString("itemsCount")).append("</rowcount>");
            }
            rs.close();
            ps.close();

            xml.append("<debug>").append(GET_ADVERTTABLE_COUNT);
            xml.append(accwareCode).append(mfrName).append(mfrNumber).append(vendorAlias);
            xml.append("</debug>");
        }
        catch(SQLException e) {
            log.error("getAdvertTable(): " + e);
        }

        return xml.toString();
    }

}
